using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using SouthernLand.Common.BOL.Articles;

namespace SouthernLand.Common.DAL.Articles
{
    public class ArticleCategoryDAL : BaseDAL
    {
        #region ObjIDataReader

        /// <summary>
        /// Returns a new ArticleCategory instance filled with the DataReader's current record data 
        /// </summary>
        private ArticleCategory GetArticleCategoryFromReader(IDataReader dr)
        {
            var obj = new ArticleCategory();
            obj.CateID = (dr["CateID"] is DBNull) ? Int32.MinValue : (Int32) dr["CateID"];
            obj.AppKey = (dr["AppKey"] is DBNull) ? String.Empty : (String) dr["AppKey"];
            obj.CateName = (dr["CateName"] is DBNull) ? String.Empty : (String) dr["CateName"];
            obj.CateName_en = (dr["CateName_en"] is DBNull) ? String.Empty : (String) dr["CateName_en"];
            obj.CateDesc = (dr["CateDesc"] is DBNull) ? String.Empty : (String) dr["CateDesc"];
            obj.ImageUrl = (dr["ImageUrl"] is DBNull) ? String.Empty : (String) dr["ImageUrl"];
            obj.CateOrder = (dr["CateOrder"] is DBNull) ? Int32.MinValue : (Int32) dr["CateOrder"];
            obj.ParentID = (dr["ParentID"] is DBNull) ? Int32.MinValue : (Int32) dr["ParentID"];
            return obj;
        }

        #endregion

        /// <summary>
        /// Returns a collection of ArticleCategory with the data read from the input DataReader
        /// </summary>
        private List<ArticleCategory> GetArticleCategoryCollectionFromReader(IDataReader dr)
        {
            var obj = new List<ArticleCategory>();
            while (dr.Read())
                obj.Add(GetArticleCategoryFromReader(dr));
            return obj;
        }

        // lay nhung loai cong viec hien thi o trang chu
        public List<ArticleCategory> GetArticleCategoryHomePageByParentID(Int32 ParentID)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                var myCommand = new SqlCommand("ArticleCategory_GetArticleCategoryHomePageByParentID", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@ParentID", SqlDbType.Int).Value = ParentID;
                SqlDataReader dr = myCommand.ExecuteReader();
                return GetArticleCategoryCollectionFromReader(dr);
            }
        }

        public int Insert(ArticleCategory obj)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                var myCommand = new SqlCommand("ArticleCategory_Insert", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@CateID", SqlDbType.Int).Value = GetAutoCateID();
                myCommand.Parameters.Add("@AppKey", SqlDbType.VarChar).Value = obj.AppKey;
                myCommand.Parameters.Add("@CateName", SqlDbType.NVarChar).Value = obj.CateName;
                myCommand.Parameters.Add("@CateName_en", SqlDbType.NVarChar).Value = obj.CateName_en;
                myCommand.Parameters.Add("@CateDesc", SqlDbType.NVarChar).Value = obj.CateDesc;
                myCommand.Parameters.Add("@ImageUrl", SqlDbType.VarChar).Value = obj.ImageUrl;
                myCommand.Parameters.Add("@CateOrder", SqlDbType.Int).Value = obj.CateOrder;
                myCommand.Parameters.Add("@ParentID", SqlDbType.Int).Value = obj.ParentID;

                return myCommand.ExecuteNonQuery();
            }
        }

        public bool Update(ArticleCategory obj)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                var myCommand = new SqlCommand("ArticleCategory_Update", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@CateID", SqlDbType.Int).Value = obj.CateID;
                myCommand.Parameters.Add("@AppKey", SqlDbType.VarChar).Value = obj.AppKey;
                myCommand.Parameters.Add("@CateName", SqlDbType.NVarChar).Value = obj.CateName;
                myCommand.Parameters.Add("@CateName_en", SqlDbType.NVarChar).Value = obj.CateName_en;
                myCommand.Parameters.Add("@CateDesc", SqlDbType.NVarChar).Value = obj.CateDesc;
                myCommand.Parameters.Add("@ImageUrl", SqlDbType.VarChar).Value = obj.ImageUrl;
                myCommand.Parameters.Add("@CateOrder", SqlDbType.Int).Value = obj.CateOrder;
                myCommand.Parameters.Add("@ParentID", SqlDbType.Int).Value = obj.ParentID;

                int ret = myCommand.ExecuteNonQuery();
                return (ret == 1);
            }
        }

        public bool Delete(Int32 CateID)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                var myCommand = new SqlCommand("ArticleCategory_Delete", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@CateID", SqlDbType.Int).Value = CateID;

                int ret = myCommand.ExecuteNonQuery();
                return (ret == 1);
            }
        }

        /// <summary>
        /// Retrieves the ArticleCategory with the specified ID
        /// </summary>
        public ArticleCategory GetArticleCategoryByID(Int32 CateID)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                var myCommand = new SqlCommand("ArticleCategory_GetArticleCategoryByID", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@CateID", SqlDbType.Int).Value = CateID;

                SqlDataReader dr = myCommand.ExecuteReader();
                return (dr.Read() ? GetArticleCategoryFromReader(dr) : null);
            }
        }

        //Get All ArticleCategory
        public List<ArticleCategory> GetAllArticleCategory(int PageIndex, int PageSize)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                var myCommand = new SqlCommand("ArticleCategory_GetAllArticleCategory", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@PageIndex", SqlDbType.Int).Value = PageIndex;
                myCommand.Parameters.Add("@PageSize", SqlDbType.Int).Value = PageSize;
                SqlDataReader dr = myCommand.ExecuteReader();
                return GetArticleCategoryCollectionFromReader(dr);
            }
        }

        // Get Count All records of Table  ArticleCategory
        public int GetCountArticleCategory()
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                var myCommand = new SqlCommand("ArticleCategory_GetCountArticleCategory", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                return (int) myCommand.ExecuteScalar();
            }
        }

        public Int32 GetAutoCateID()
        {
            Int32 value;
            try
            {
                using (SqlConnection myConn = GetConnectionString())
                {
                    var myCommand = new SqlCommand("ArticleCategory_GetAutoCateID", myConn);
                    myCommand.CommandType = CommandType.StoredProcedure;

                    value = ((Int32) myCommand.ExecuteScalar() + 1);
                    if (value == 1)
                        value = 2; // vi khong de trung voi ParentID = 1

                    return value;
                }
            }
            catch
            {
                return 2;
            }
        }

        public List<ArticleCategory> GetArticleCategoryByParentID(Int32 ParentID)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                var myCommand = new SqlCommand("ArticleCategory_GetArticleCategoryByParentID", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@ParentID", SqlDbType.Int).Value = ParentID;
                SqlDataReader dr = myCommand.ExecuteReader();
                return GetArticleCategoryCollectionFromReader(dr);
            }
        }
    }
}